
using System;
using System.Data;

namespace stefc.geo.data
{
	/// <summary>
	/// Handles Schema Upgrade as one static method 
	/// </summary>
	internal class SchemaUpgrade
	{
		private const long SCHEMA_VERSION = 2;
		
		private const string PRAGMA_VERSION = "user_version";
		
		private static string[] SCHEMA_DDL = {
// V1
@"CREATE TABLE contacts (
	Id    		INTEGER PRIMARY KEY AUTOINCREMENT, 
	Name  		TEXT, 
	JId  		TEXT,
	Level 		INTEGER
);

-- Insert Root 
INSERT INTO contacts (Name,JId,Level) VALUES('Me', 'myself@jabber.org/home', 0);

CREATE TABLE settings (
	Key   		TEXT PRIMARY KEY NOT NULL, 
	Value 		TEXT
);

CREATE TABLE locations (
	Id			INTEGER PRIMARY KEY,
	ContactId 	INTEGER NOT NULL REFERENCES contacts (Id),
	Date		DATE,
	GPX 		BLOB
);

CREATE INDEX ix_locations_date ON locations (Date);
",

// V2
@"CREATE TABLE pois (
	Id    		INTEGER PRIMARY KEY AUTOINCREMENT, 
	Name  		TEXT, 
	Location	TEXT,
	Color 		INTEGER
);

-- Insert some POI  
INSERT INTO pois (Name,Location,Color) VALUES('Oslo', '59°54'' 10°44''', 0);
INSERT INTO pois (Name,Location,Color) VALUES('New York', '40°44'' -74°02''', 0);
INSERT INTO pois (Name,Location,Color) VALUES('Madrid', '40°25'' -3°42''', 0);
INSERT INTO pois (Name,Location,Color) VALUES('Tokio', '35°40'' 139°35''', 0);
"
		};
		
		public static long Upgrade(DbHelper helper)
		{
			long currentVersion = helper.GetPragma(PRAGMA_VERSION);
			if(currentVersion < SCHEMA_VERSION)
			{
				IDbTransaction tx = helper.BeginTransaction();
				try
				{
					while(currentVersion<SCHEMA_VERSION)
					{
						helper.CreateCommand(tx,SCHEMA_DDL[currentVersion]).ExecuteNonQuery();
						currentVersion++;
					}				
					helper.SetPragma(tx,PRAGMA_VERSION, SCHEMA_VERSION);
					tx.Commit();	
				}
				catch(Exception e)
				{
					tx.Rollback();
					throw e;
				}	
				
				return SCHEMA_VERSION;	// Updating to current Version of Schema		
			}
			else
				return -1;	// no  Update of Schema
		}
	}
}
